Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!
(Publisher: The Coriolis Group)
Author(s): Peter G. Aitken
ISBN: 1576102815
Publication Date: 08/01/98

Listing 19.14 Objects and properties in MUSIC.FRM.

Begin VB.Form Form1
   Caption         =   “Music Database”
   Begin VB.CommandButton Command1
      Caption             =   “E&xit”
      Index               =   4
   End
   Begin VB.CommandButton Command1
      Caption             =   “&List”
      Index               =   3
   End
   Begin VB.CommandButton Command1
      Caption             =   “&Find a Record”
      Index               =   2
   End
   Begin VB.CommandButton Command1
      Caption             =   “&Delete Current Record”
      Index               =   1
   End
   Begin VB.CommandButton Command1
      Caption             =   “&Add a Record”
      Index               =   0
   End
   Begin VB.TextBox txtNotes
      DataField                  =   “Notes”
      DataSource                 =   “Data1”
   End
   Begin VB.TextBox txtLocation
      DataField           =   “Location”
      DataSource          =   “Data1”
   End
   Begin VB.TextBox txtPublisher
      DataField           =   “Publisher”
      DataSource          =   “Data1”
   End
   Begin VB.TextBox txtMedia
      DataField           =   “Media”
      DataSource          =   “Data1”
   End
   Begin VB.TextBox txtTitle
      DataField           =   “Title”
      DataSource          =   “Data1”
   End
   Begin VB.TextBox txtComposer
      DataField           =   “Composer/Group”
      DataSource          =   “Data1”
   End
   Begin VB.Data Data1
      Connect             =   “Access”
      Exclusive           =   0   ‘False
      Options             =   0
      RecordsetType       =   1  ‘Dynaset
   End
   Begin VB.Label Label6
      Alignment           =   1  ‘Right Justify
      Caption             =   “Notes:”
   End
   Begin VB.Label Label5
      Alignment           =   1  ‘Right Justify
      Caption             =   “Location:”
   End
   Begin VB.Label Label4
      Alignment           =   1  ‘Right Justify
      Caption             =   “Publisher:”
   End
   Begin VB.Label Label3
      Alignment           =   1  ‘Right Justify
      Caption             =   “Media:”
   End
   Begin VB.Label Label2
      Alignment           =   1  ‘Right Justify
      Caption             =   “Title:”
   End
   Begin VB.Label Label1
      Alignment           =   1  ‘Right Justify
      Caption             =   “Composer/group:”
   End
End

Figure 19.10  The database program’s main form.

Binding Controls To The Data Table

Once the form has been designed, you need to bind the controls to the data. This requires two steps:

1.  Bind the Data control to a specific database file and to a specific table within that file.

2.  Bind each data-aware control to the Data control and a specific field in the table to which the Data control is bound.

As you might expect, you do this with the control properties. For the Data control, set its DatabaseName property to the database you just created, MUSIC.MDB, and set its RecordSource property to Recordings. (Because a database can have more than one table, you must specify the exact table to which the Data control is linked.) Also, set the Data control’s RecordsetType property to 1 -Dynaset (more on Recordsets and Dynasets soon). For each of the six Text Box controls, set the DataSource property to Data1, pointing at the Data control. Then, set the DataField property of each control to the name of its associated field: Composer/Group for the first Text Box, Title for the second, and so on.

Although the program is not complete, we can run it and see the power of data-aware controls in action. The “dummy” record we entered in the database from the Visual Data Manager will be displayed in the Text Box controls. Had we entered multiple records, we would be able to move among them by clicking on the arrows on the Data control—all this without a single line of code. We do, however, need some code—although surprisingly little—to complete the database program’s functionality.

Adding The Remaining Code

The code for the array of Command Buttons is remarkably simple, largely because of the power of the database engine. This code is shown in Listing 19.15. To add a new record, you just call the AddNew method of the Recordset object associated with the Data control. Other than the convenient touch of setting the focus to the first Text Box, that’s all there is to it. The AddNew method takes care of clearing the Text Boxes in preparation for entry of new data, as well as saving the new data once it has been entered. Compare this with the code for adding a new record in this chapter’s first program—what a difference.

Deleting a record is equally easy. Executing the Recordset object’s Delete method is all that is required. Follow this with the MoveNext method, which displays the next record, so the form’s Text Boxes do not remain blank.

Finding a particular record requires a call to the FindRecord procedure, which we’ll create soon. Likewise, for listing all records, you rely on the Show method to display a separate form.

Listing 19.15 The Command Button Click event code.

Private Sub Command1_Click(Index As Integer)

Select Case Index
    Case 0      ‘ Add
        txtComposer.SetFocus
        Data1.Recordset.AddNew
    Case 1      ‘ Delete
        Data1.Recordset.Delete
        Data1.Recordset.MoveNext
    Case 2      ‘ Find
        Call FindRecord
    Case 3      ‘ List
        lstForm.Show
    Case 4      ‘ Exit
        End
End Select

End Sub

Recordsets

A Recordset is a type of Visual Basic object that acts as an abstract representation of an actual database table on disk. Your program accesses and manipulates the data in the table by means of the Recordset object, which has its own properties and methods. When a form containing a Data control is loaded, and if that Data control is validly linked to a table in a database, a Recordset object is automatically created and available for use by means of the Data control’s Recordset property. A Recordset can also be created in code—independent of a Data control—but we will not explore this technique in this chapter. For a complete look at the many properties and methods of the Recordset object, refer to the Visual Basic Help system.